package Test2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;

public class JDBCUile { 

	private static ResourceBundle rb = ResourceBundle.getBundle("Test2.jdbc");
	private static String classname = rb.getString("driver");
	private static String url = rb.getString("url");
	private static String username = rb.getString("username");
	private static String password = rb.getString("password");

	static {
		try {
			Class.forName(classname);
			System.out.println("驱动加载成功");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			System.out.println("驱动加载失败");
			e.printStackTrace();
		}

	}

	public static void exClose(ResultSet resultSet, Statement statement, Connection connection) {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	// 获得连接
	public static Connection getConnection() {
		Connection connection = null;
		try {
			connection = DriverManager.getConnection(url, username, password);
			//System.out.println("数据库连接成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.err.println("数据库连接失败");
			e.printStackTrace();
		}
		return connection;

	}
	// 插入 单个书

	public void insertSingleBook(Book book) {
		String sql = "insert into book values(?,?,?)";
		Connection connection = getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		Integer id = book.getId();
		String name = book.getName();
		Integer repertory = book.getRepertory();

		int r = 0;
		try {
			ps.setInt(1, id);
			ps.setString(2, name);
			ps.setInt(3, repertory);
			r = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(r);
		exClose(null, ps, connection);

	}

	// 插入 单个借书卡

	public void insertSingleCard(Card card) {
		String sql = "insert into card values(?,?,?,?)";
		Connection connection = getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		Integer id = card.getId();
		String name = card.getName();
		String classroom = card.getClassRoom();
		String major = card.getMajor();
		
		try {
			ps.setInt(1, id);
			ps.setString(2, name);
			ps.setString(3, classroom);
			ps.setString(4, major);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		exClose(null, ps, connection);

	}

	
	// 插入借书卡记录
	public void insertSingleCardRecord(CardRecord cardRecord) {
		String sql = "insert into cardrecord(card_id,card_name,book_id,book_name,time_start,time_end) values(?,?,?,?,?,?)";
		Connection connection = getConnection();
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		Integer cardId = cardRecord.getCardId();
		String  cardname =cardRecord.getName();
		Integer bookId = cardRecord.getBookId();
         String bookname =cardRecord.getBookName();
		String timeStart = cardRecord.getTimeStart();
		String timeEnd = cardRecord.getTimeEnd();

		int r = 0;
		try {
			ps.setInt(1, cardId);
			ps.setString(2, cardname);
			ps.setInt(3, bookId);		
			ps.setString(4, bookname);		
			ps.setString(5, timeStart);
			ps.setString(6, timeEnd);
			r = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(r);
		exClose(null, ps, connection);

	}

	// 删除书籍
	public static void delteBook(Book book) {

		Connection connection = getConnection();
		String sql = "delete from book where id=?";
		PreparedStatement ps = null;

		try {
			ps = connection.prepareStatement(sql);
			int id = book.getId();
			ps.setInt(1, id);
			ps.execute();

			System.out.println("删除成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(null, ps, connection);

	}

	// 删除借书卡
	public static void delteBook(Card card) {

		Connection connection = getConnection();
		String sql = "delete from card where id=?";
		PreparedStatement ps = null;

		try {
			ps = connection.prepareStatement(sql);
			int id = card.getId();
			ps.setInt(1, id);
			ps.execute();

			System.out.println("删除成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(null, ps, connection);

	}

	// 查询某书
	public static List<Object> appendWhereBook(StringBuffer sql, Book book) {
		List<Object> list = new ArrayList<>();
		Integer id = book.getId();
		if (id != 0) {
			sql.append("and id = ? ");
			list.add(id);

		}
		String name = book.getName();
		if (name != null && !name.trim().isEmpty()) {
			sql.append("and name = ? ");
			list.add(name);
		}

		return list;

	}

	// 按照条件查询借书卡信息
	public static Card selectSingleCard(Card card) throws Exception {
		StringBuffer sql = new StringBuffer("select ");
		sql.append("id,name,classroom, major from card where 1=1 ");
		List<Object> list = appendWhereCard(sql, card);

		Connection connection = null;

		ResultSet rs = null;
		connection = getConnection();

		PreparedStatement ps = null;

		ps = connection.prepareStatement(sql.toString());

		for (int i = 0; i < list.size(); i++) {
			ps.setObject(i + 1, list.get(i));
		}

		rs = ps.executeQuery();

		Card card2 = new Card();
		while (rs.next()) {
			card2.setId(rs.getInt("id"));
			card2.setName(rs.getString("name"));
			card2.setClassRoom(rs.getString("classroom"));
			card2.setMajor(rs.getString("major"));

		}

		exClose(rs, ps, connection);

		return card2;

	}

	// 查询某卡
	public static List<Object> appendWhereCard(StringBuffer sql, Card card) {
		List<Object> list = new ArrayList<>();
		Integer id = card.getId();
		if (id != 0) {
			sql.append("and id = ? ");
			list.add(id);

		}
		String name = card.getName();
		if (name != null && !name.trim().isEmpty()) {
			sql.append("and name = ? ");
			list.add(name);
		}
		String classroom = card.getClassRoom();
		if (classroom != null && !classroom.trim().isEmpty()) {
			sql.append("and classroom = ? ");
			list.add(classroom);
		}
		String major = card.getMajor();
		if (major != null && !major.trim().isEmpty()) {
			sql.append("and major = ? ");
			list.add(major);
		}

		return list;

	}

	// 按照条件查询图书库存信息
	public static Book selectSingleBook(Book book) {
		StringBuffer sql = new StringBuffer("select ");
		sql.append("id,name,repertory from book where 1=1 ");
		List<Object> list = appendWhereBook(sql, book);

		Connection connection = null;

		ResultSet rs = null;
		connection = getConnection();

		PreparedStatement ps = null;
		Book book2 = new Book();
		try {
			ps = connection.prepareStatement(sql.toString());
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}
			
			rs = ps.executeQuery();
			
			
			while (rs.next()) {
				book2.setId(rs.getInt("id"));
				book2.setName(rs.getString("name"));
				book2.setRepertory(rs.getInt("repertory"));
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("查询失败，该书不存在");
			e.printStackTrace();
		}


		exClose(rs, ps, connection);

		return book2;

	}

	
	// 查询某图书的借阅记录
	public static ArrayList<CardRecord> selectBookRecord(String bookname ,int bookid) {

	    
		String sql = "select * from cardrecord where book_id=? and book_name=?";

		Connection connection = null;

		ResultSet rs = null;
		connection = getConnection();

	

		PreparedStatement ps = null;

		ArrayList<CardRecord> bookRecords = new ArrayList<>();
		try {
			ps = connection.prepareStatement(sql);
			ps.setInt(1, bookid);
			ps.setString(2, bookname);
			
			rs = ps.executeQuery();
			while (rs.next()) {
				CardRecord cardRecords2 = new CardRecord();
				cardRecords2.setCardId(rs.getInt("card_id"));
				cardRecords2.setName(rs.getString("card_name"));
				cardRecords2.setBookId(rs.getInt("book_id"));
				cardRecords2.setBookName(rs.getString("book_name"));
				cardRecords2.setTimeStart(rs.getString("time_start"));
				cardRecords2.setTimeEnd(rs.getString("time_end"));
				
				bookRecords.add(cardRecords2);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		exClose(rs, ps, connection);

		return bookRecords;

	}
	
	// 查询某图书卡的借阅记录
		public static ArrayList<CardRecord> selectAllCardRecords(String name, int id){

			
			String sql = "select * from cardrecord where card_name=? and card_id=?";

			Connection connection = null;

			ResultSet rs = null;
			connection = getConnection();

			ArrayList<CardRecord> cardRecords = new ArrayList<>();

			PreparedStatement ps = null;

			try {
				ps = connection.prepareStatement(sql);
				ps.setString(1, name);
				ps.setInt(2, id);
				
				rs = ps.executeQuery();
				
				while (rs.next()) {
					CardRecord cardRecords2 = new CardRecord();
					cardRecords2.setCardId(rs.getInt("card_id"));
					cardRecords2.setName(rs.getString("card_name"));
					cardRecords2.setBookId(rs.getInt("book_id"));
					cardRecords2.setBookName(rs.getString("book_name"));
					cardRecords2.setTimeStart(rs.getString("time_start"));
					cardRecords2.setTimeEnd(rs.getString("time_end"));
					
					cardRecords.add(cardRecords2);
					
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			exClose(rs, ps, connection);

			return cardRecords;

		}
	
	
	
	// 添加还书时间（借书卡）

	public static int updateCardRecord(CardRecord cardRecord) {
		Integer cardId = cardRecord.getCardId();
		Integer bookId = cardRecord.getBookId();
		String timeEnd = cardRecord.getTimeEnd();

		String sql = "update cardrecord set time_end=? where book_id =? and card_id=?";

		Connection connection = null;
		connection = getConnection();

		PreparedStatement ps = null;
		int flag =0;
		try {
			ps = connection.prepareStatement(sql);
			ps.setString(1, timeEnd);
			ps.setInt(2, bookId);
			ps.setInt(3, cardId);
		    flag =	ps.executeUpdate();
		    
			System.out.println("借书卡记录更新");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("更新失败");
			e.printStackTrace();
		}
 
		exClose(null, ps, connection);
		return flag;

	}
	
	
	
	// 修改图书库存

		public static void updateBook(Book book) {
			
			Integer id = book.getId();
			Integer repetory = book.getRepertory();
			String name = book.getName();

			String sql = "update book set repertory=? where id =? and name=?";

			Connection connection = null;
			connection = getConnection();

			PreparedStatement ps = null;
			try {
				ps = connection.prepareStatement(sql);
				ps.setInt(1, repetory);
				ps.setInt(2, id);
				ps.setString(3, name);

				ps.executeUpdate();
				System.out.println("图书库存更新");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			exClose(null, ps, connection);

		}
	

	// 按照借阅次数进行排序
	public static ArrayList<StringBuffer> selectBookBorrowSum() {
		
        String sql ="select b.*,p.count from book as b\r\n"
        		+ "inner join\r\n"
        		+ "(select book_id,count(book_id) as count from cardrecord \r\n"
        		+ "group by book_id \r\n"
        		+ "order by count) p\r\n"
        		+ "on b.id =p.book_id";
        Connection connection =getConnection();
        
        PreparedStatement ps =null; 
        ResultSet rs = null;
        ArrayList<StringBuffer>  Lists = new ArrayList<>();
		try {
			 ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			
			
			while(rs.next()) {
				int id = rs.getInt("id");
				String bookname = rs.getString("name");
				int repetory = rs.getInt("repertory");
				int count = rs.getInt("count");
				StringBuffer sb = new StringBuffer();
				sb.append("书的编号： "+id+" 书的名称："+bookname+" 库存为 ："+repetory+" 被借次数： "+count);
				
				Lists.add(sb);
				
			}
			ps = connection.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
		exClose(rs, ps, connection);
        return Lists;
        
}
}